	package org.judo.generate.crud;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;

import org.judo.admin.identity.AppUser;
import org.judo.database.JudoDBConnection;
import org.judo.database.TypeTranslator;
import org.judo.database.exceptions.DatabaseException;
import org.judo.database.exceptions.ORMException;
import org.judo.database.profile.ColumnSchema;
import org.judo.database.profile.TableSchema;
import org.judo.propertyobject.PropertyObject;
import org.judo.util.JavaNameConversion;

public class CrudGenerator
{
	private HashMap<String,TableSchema> tables;
	private String path = "";
	private String scriptPath = "";
	private String javaName;
	
	public Iterator tableNames()
	{
		return tables.keySet().iterator();
	}
	
	public TableSchema getTableSchema(String tableName) 
		throws DatabaseException, ORMException
	{
		TableSchema table = tables.get(tableName);
		return table;
	}
	
	public CrudGenerator(Connection con) 
		throws DatabaseException
	{
		try
		{
			tables = new HashMap<String,TableSchema>();
			
			DatabaseMetaData metaData = con.getMetaData();		
			ResultSet rs = metaData.getTables(null,"%","%",new String[]{"TABLE"});
			
			while(rs.next())
			{
				TableSchema table = new TableSchema();
				table.tableName = rs.getString("TABLE_NAME");
				
				tables.put(table.tableName, table);
				ResultSet colRS = metaData.getColumns(null,"%",table.tableName,"%");
				
				while (colRS.next())
				{
					ColumnSchema col = new ColumnSchema();
					
					col.name = colRS.getString("COLUMN_NAME");
					col.type = TypeTranslator.getJavaType(colRS.getInt("DATA_TYPE"));
					
					int nullable = colRS.getInt("NULLABLE");
					
					if (nullable == DatabaseMetaData.columnNoNulls)
						col.isNullable = false;
					else
						col.isNullable = true;
					
					table.columns.put(col.name,col);
					table.columnList.add(col);
				}
				
				ResultSet pkrs = metaData.getPrimaryKeys("%","%",table.tableName);
				
				while (pkrs.next())
				{		
					String priamryKey = pkrs.getString("COLUMN_NAME");
					table.primaryKeys.add(priamryKey);
					
					ColumnSchema col = table.columns.get(priamryKey);
					col.isPrimaryKey = true;
				}
			}
			
			Iterator names = tables.keySet().iterator();
			
			Statement stmnt = null;
			ResultSet metaRs = null;
			
			try
			{
				stmnt = con.createStatement();
				
				while (names.hasNext())
				{
					String tableName = (String)names.next();
					String sql = "select * from "+tableName+" where 1 = 0";
					metaRs = stmnt.executeQuery(sql);
					
					TableSchema table = (TableSchema)tables.get(tableName);
					ResultSetMetaData rsmd = metaRs.getMetaData();
					
					int count = rsmd.getColumnCount();
					
					for (int i=0;i<count;i++)
					{
						int col = i+1;
						String colName = rsmd.getColumnName(col);
						boolean isAutoIncrement = rsmd.isAutoIncrement(col);
						
						ColumnSchema column = table.columns.get(colName);	
						column.isAutoIncrement =  isAutoIncrement;
					}
					
					metaRs.close();
				}
			}
			finally
			{
				if (stmnt != null)
					stmnt.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new DatabaseException("Error getting database meta data: "+e);
		}
	}
	
	private static String tabs(int count)
	{
		String tabs = "";
		
		for (int i=0;i<count;i++)
		{
			tabs += "\t";
		}
		
		return tabs;
	}
	
	public String generateHTMLTable(JudoDBConnection con, String name,String listName) throws DatabaseException, ORMException
	{
		String code = tabs(3)+"<table class=\"standardTBL\">\n";
		
		TableSchema schema = getTableSchema(name);
		
		code += tabs(4)+"<tr class=\"standardTBLHeader\">\n";
		
		for (ColumnSchema col:schema.columnList)
		{
			code += tabs(5)+"<td>"+javaTable(col.name)+"</td>\n";
		}
		
		code += tabs(4)+"</tr>\n";
		
		code += tabs(4)+"<c:forEach var=\"cur\" items=\"${"+listName+"}\">\n";
		code += tabs(4)+"<tr>\n";
		for (ColumnSchema col:schema.columnList)
		{
			if (schema.primaryKeys.contains(col.name) && schema.primaryKeys.size() == 1)
				code += tabs(5)+"<td><a href=\"${rootContext}/"+path+javaTable(name)+"_controller/detail_"+javaCol(name)+".do?id=${cur."+javaCol(col.name)+"}\">${cur."+javaCol(col.name)+"}</a></td>\n";
			else
				code += tabs(5)+"<td>${cur."+javaCol(col.name)+"}</td>\n";
		}
		code += tabs(4)+"</tr>\n";
		code += tabs(4)+"</c:forEach>\n";
		
		code += tabs(3)+"</table>\n";
		
		return code;
	}
	
	public String generateScript(String tableName) throws DatabaseException, ORMException
	{
		javaName = JavaNameConversion.getClassName(tableName);
		String script = "";
		TableSchema schema = getTableSchema(tableName);
		
		script += model(tableName,schema);
		script += display(tableName,schema);
		script += create(tableName,schema);
		
		if (schema.primaryKeys.size() == 1)
			script += detail(tableName,schema);
		
		if (schema.primaryKeys.size() == 1)
			script += update(tableName,schema);
		
		if (schema.primaryKeys.size() == 1)
			script += delete(tableName,schema);
		
		return script;
	}
	
	private String delete(String tableName, TableSchema schema)
	{
		String script = "";
		
		script += "action:"+javaTable(tableName)+"_controller/delete_"+javaCol(tableName)+"\n";
		script += "\tshowPage(\"/"+path+javaTable(tableName)+"_controller/display_"+javaCol(tableName)+".do\");\n";
		
		ColumnSchema column = schema.columns.get(schema.primaryKeys.get(0));
		
		if (isQuotedType(column.type))
		{
			script += "\tappDB.delete(this,\"delete from "+tableName+" where "+schema.primaryKeys.get(0)+" = '${id}'\");\n";		
		}
		else
		{
			script += "\tappDB.delete(this,\"delete from "+tableName+" where "+schema.primaryKeys.get(0)+" = ${id}\");\n";
		}
	
		script += "/action\n";
		return script;
	}

	private String update(String tableName, TableSchema schema)
	{
		String script = "";
		script += "view:"+javaTable(tableName)+"_controller/edit_"+javaCol(tableName)+"\n";
		
		if (!scriptPath.equals(""))
			script += "\tpath:"+scriptPath+"\n";
		
		script += "\ttitle:Update "+javaTable(tableName)+"\n";
		script += "\th3:Update New "+javaTable(tableName)+"\n";
		
		script += "\t<a href=\"ROOT/"+path+javaTable(tableName)+"_controller/display_"+javaCol(tableName)+".do\">View All "+javaTable(tableName)+"</a>\n";
		script += "\t\n";
		
		ColumnSchema column = schema.columns.get(schema.primaryKeys.get(0));
		
		script += "\taction:\n";
		
		if (isQuotedType(column.type))
		{
			script += "\t\tPropertyObject obj = appDB.find(parse(\"select * from "+
				javaTable(tableName)+" where "+schema.primaryKeys.get(0)+" = '${id}'\"));\n";
		}
		else
		{
			script += "\t\tPropertyObject obj = appDB.find(parse(\"select * from "+
				javaTable(tableName)+" where "+schema.primaryKeys.get(0)+" = ${id}\"));\n";
		}
		
		script += "\t\tcopyProps(obj);\n";
		script += "\t/action\n";
		
		script += "\tform:"+javaTable(tableName)+"_controller/update_"+javaCol(tableName)+"\n";
		
		if (!scriptPath.equals(""))
			script += "\t\tpath:"+scriptPath+"\n";
		
		script += "\t\tforward:"+path+javaTable(tableName)+"_controller/display_"+javaCol(tableName)+"\n";
		script += "\t\tpopulate:\n";
		
		ColumnSchema pkcol =schema.columns.get(schema.primaryKeys.get(0));
		
		if (pkcol.type.getSimpleName().equals("String"))
			script += "\t\thidden:id,"+javaCol(schema.primaryKeys.get(0))+"\n";
		else if (pkcol.type.getSimpleName().equals("Date") || pkcol.type.getSimpleName().equals("Timestamp"))
			script += "\t\thidden:id,"+javaCol(schema.primaryKeys.get(0))+",date\n";
		else if (pkcol.type.getSimpleName().equals("Integer"))
			script += "\t\thidden:id,"+javaCol(schema.primaryKeys.get(0))+",int\n";
		else if (pkcol.type.getSimpleName().equals("Float") || pkcol.type.getSimpleName().equals("Double"))
			script += "\t\thidden:id,"+javaCol(schema.primaryKeys.get(0))+",float\n";
		
		for (ColumnSchema col:schema.columnList)
		{
			if (schema.primaryKeys.contains(col.name))
			{
				script += "\t\thidden:"+javaCol(col.name)+"\n";
			}
			else if (col.type.getSimpleName().equals("String"))
				script += "\t\tstring:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
			else if (col.type.getSimpleName().equals("Date") || col.type.getSimpleName().equals("Timestamp"))
				script += "\t\tdate:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
			else if (col.type.getSimpleName().equals("Integer"))
				script += "\t\tint:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
			else if (col.type.getSimpleName().equals("Float") || col.type.getSimpleName().equals("Double"))
				script += "\t\tfloat:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
		}
		
		script += "\t\taction:\n";
		
		script += "\t\t\t"+javaName+" obj = ("+javaName+")appDB.find("+javaName+".class,id);\n";
		script += "\t\t\tobj.copyProps(this);\n";
		script += "\t\t\tappDB.update(\""+tableName+"\",obj);\n";
		
		script += "\t\t/action\n";
		
		script += "\t/form\n";
		script += "/view\n";
		return script;
	}

	private String detail(String tableName, TableSchema schema)
	{
		String script = "";
		
		script += "view:"+javaTable(tableName)+"_controller/detail_"+javaCol(tableName)+"\n";
		
		if (!scriptPath.equals(""))
			script += "\tpath:"+scriptPath+"\n";
		
		String edit = "";
		
		if (schema.primaryKeys.size() == 1)
		{
			edit += " | <a href=\"ROOT/"+path+javaTable(tableName)+"_controller/edit_"+javaCol(tableName)+".do?id=${"+javaCol(schema.primaryKeys.get(0))+"}\">"+			
			"<img src=\"ROOT/images/icon_edit.png\" border=0 alt=\"Edit\"> Edit This Record</a> ";
			
			edit += " | <a href=\"ROOT/"+path+javaTable(tableName)+"_controller/delete_"+javaCol(tableName)+".do?id=${"+javaCol(schema.primaryKeys.get(0))+"}\"><img src=\"ROOT/images/icon_delete.png\" border=0 alt=\"Delete\"> Delete This Record</a>\n";
		}
		
		script += "\ttitle:"+javaTable(tableName)+" Details\n";
		script += "\th3:"+javaTable(tableName)+" Details\n";
		script += "\t<a href=\"ROOT/"+path+javaTable(tableName)+"_controller/display_"+javaCol(tableName)+".do\">View All "+javaTable(tableName)+"</a> "+edit+" \n";
		
		ColumnSchema col = schema.columns.get(schema.primaryKeys.get(0));
		
		script += "\taction:\n";
		if (isQuotedType(col.type))
		{
			script += "\t\tPropertyObject obj = appDB.find(parse(\"select * from "+tableName+" where "+schema.primaryKeys.get(0)+" = '${id}'\"));\n";
		}
		else
		{
			script += "\t\tPropertyObject obj = appDB.find(parse(\"select * from "+tableName+" where "+schema.primaryKeys.get(0)+" = ${id}\"));\n";
		}
		script += "\t\tcopyProps(obj);\n";
		script += "\t/action\n";
		
		script += "\tdisplay:100,200\n";
		
		for (ColumnSchema column:schema.columnList)
		{			
			if (column.type.getSimpleName().equals("String"))
				script += "\t\t"+javaTable(column.name)+",${"+javaCol(column.name)+"}\n";
			else if (column.type.getSimpleName().equals("Date") || column.type.getSimpleName().equals("Timestamp"))
				script += "\t\t"+javaTable(column.name)+",${date|"+javaCol(column.name)+"}\n";
			else if (column.type.getSimpleName().equals("Integer"))
				script += "\t\t"+javaTable(column.name)+",${int|"+javaCol(column.name)+"}\n";
			else if (column.type.getSimpleName().equals("Float") || column.type.getSimpleName().equals("Double"))
				script += "\t\t"+javaTable(column.name)+",${float|"+javaCol(column.name)+"}\n";
		}
		
		script += "\t/display\n";
		script += "/view\n";
		
		return script;
	}

	private boolean isQuotedType(Class type)
	{
		if (Integer.class.isAssignableFrom(type))
			return false;
		
		if (Double.class.isAssignableFrom(type))
			return false;
		
		if (Float.class.isAssignableFrom(type))
			return false;
		
		if (Short.class.isAssignableFrom(type))
			return false;
		
		if (Long.class.isAssignableFrom(type))
			return false;
		
		if (Boolean.class.isAssignableFrom(type))
			return false;
		
		return true;
	}
	
	private String create(String tableName, TableSchema schema)
	{
		String script = "";
		
		script += "view:"+javaTable(tableName)+"_controller/create_"+javaCol(tableName)+"\n";
		
		if (!scriptPath.equals(""))
			script += "\tpath:"+scriptPath+"\n";
		
		script += "\ttitle:Create "+javaTable(tableName)+"\n";
		script += "\th3:Create New "+javaTable(tableName)+"\n";
		
		script += "\tform:"+javaTable(tableName)+"_controller/insert_"+javaCol(tableName)+"\n";
		
		if (!scriptPath.equals(""))
			script += "\t\tpath:"+scriptPath+"\n";
		
		script += "\t\tforward:"+path+javaTable(tableName)+"_controller/display_"+javaCol(tableName)+"\n";
		
		for (ColumnSchema col:schema.columnList)
		{
			if (schema.primaryKeys.contains(col.name))
				continue;
			
			if (col.type.getSimpleName().equals("String"))
				script += "\t\tstring:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
			else if (col.type.getSimpleName().equals("Date") || col.type.getSimpleName().equals("Timestamp"))
				script += "\t\tdate:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
			else if (col.type.getSimpleName().equals("Integer"))
				script += "\t\tint:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
			else if (col.type.getSimpleName().equals("Float") || col.type.getSimpleName().equals("Double"))
				script += "\t\tfloat:"+javaCol(col.name)+","+javaTable(col.name)+":\n";
		}
		
		script += "\t\taction:\n";
		
		script += "\t\t\t"+javaName+" obj = new "+javaName+"();\n";
		script += "\t\t\tobj.copyProps(this);\n";
		script += "\t\t\tappDB.insert(\""+tableName+"\",obj);\n";
		
		script += "\t\t/action\n";
		
		script += "\t/form\n";
		script += "/view\n";
		
		return script;
	}

	private String display(String tableName, TableSchema schema)
	{
		String script = "";
		
		script += "view:"+javaTable(tableName)+"_controller/display_"+javaCol(tableName)+"\n";
		
		if (!scriptPath.equals(""))
			script += "\tpath:"+scriptPath+"\n";
		
		script += "\tdefault_action:\n";
		script += "\tmenu:judo_menu,"+javaTable(tableName).replace("_"," ")+"\n";
		script += "\taction:\n";
		script += "\t\tresults = (ArrayList)appDB.query(\"select * from "+tableName+"\");\n";
		script += "\t/action\n";
		
		script += "\ttitle:View All "+javaTable(tableName)+"\n";
		script += "\th3:View All "+javaTable(tableName)+"\n";
		
		//if (schema.primaryKeys.size() == 1)
		//{
			script += "\n";
			script += "\t<a href=\"ROOT/"+path+javaTable(tableName)+"_controller/create_"+javaCol(tableName)+".do\">Create New "+javaTable(tableName)+"</a>\n";
			script += "\n";
		//}
		
		script += "\tdata_table:\n";
		script += "\t\titems:results\n";
		
		script += "\t\theaders:\n";
		for (ColumnSchema col:schema.columnList)
		{
			script += "\t\t\t"+javaTable(col.name)+"\n";
		}
		
		if (schema.primaryKeys.size() == 1)
			script += "\t\t\tActions\n";
		
		script += "\t\t/headers\n";
		
		script += "\t\tvalues:\n";
		for (ColumnSchema col:schema.columnList)
		{
			if (schema.primaryKeys.contains(col.name) && schema.primaryKeys.size() == 1)
				script += "\t\t\t<a href=\"ROOT/"+path+javaTable(tableName)+"_controller/detail_"+javaCol(tableName)+".do?id=${cur."+javaCol(col.name)+"}\">${cur."+javaCol(col.name)+"}</a>\n";
			else
				script += "\t\t\t${cur."+javaCol(col.name)+"}\n";
		}
		
		if (schema.primaryKeys.size() == 1)
		{
			script += "\t\t\t<a href=\"ROOT/"+path+javaTable(tableName)+"_controller/edit_"+javaCol(tableName)+".do?id=${cur."+javaCol(schema.primaryKeys.get(0))+"}\">"+			
			"<img src=\"ROOT/images/icon_edit.png\" border=0 alt=\"Edit\"></a> "+			
			"<a href=\"ROOT/"+path+javaTable(tableName)+"_controller/delete_"+javaCol(tableName)+".do?id=${cur."+javaCol(schema.primaryKeys.get(0))+"}\"><img src=\"ROOT/images/icon_delete.png\" border=0 alt=\"Delete\"></a>\n";
		}
		else
			script += "\t\t\t&nbsp;\n";
		
		script += "\t\t/values\n";
		script += "\t/data_table\n";
		script += "/view\n";
		
		return script;
	}

	private String model(String tableName,TableSchema schema)
	{
		String script = "";
		
		script += "model:"+javaName+"\n";
		script += "\tprops:\n";
		
		for (ColumnSchema col:schema.columnList)
		{
			if (col.type.getSimpleName().equals("String"))
				script += "\t\tstring:"+javaCol(col.name)+","+javaTable(col.name)+" Value\n";
			else if (col.type.getSimpleName().equals("Date") || col.type.getSimpleName().equals("Timestamp"))
				script += "\t\tdate:"+javaCol(col.name)+","+javaTable(col.name)+" Value\n";
			else if (col.type.getSimpleName().equals("Integer"))
				script += "\t\tint:"+javaCol(col.name)+","+javaTable(col.name)+" Value\n";
			else if (col.type.getSimpleName().equals("Float") || col.type.getSimpleName().equals("Double"))
				script += "\t\tdouble:"+javaCol(col.name)+","+javaTable(col.name)+" Value\n";
		}
		
		script += "\t/props\n";	
		script += "/model\n";
		
		return script;
	}

	public String javaTable(String str)
	{
		str = str.toLowerCase();
		char chs[] = str.toCharArray();
		chs[0] = Character.toUpperCase(chs[0]);
		str = new String(chs);
		
		return str;
	}
	
	public String javaCol(String str)
	{
		str = str.toLowerCase();
		return str;
	}

	public void setPath(String path)
	{
		this.path = path+"/";
		scriptPath = path;
	}

	public String generateModelScript(String tableName) throws DatabaseException, ORMException
	{
		javaName = JavaNameConversion.getClassName(tableName);
		String script = "";
		TableSchema schema = getTableSchema(tableName);
		
		script += model(tableName,schema);
		return script;
	}
}
